﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO = Microsoft.MSE.Catalog.Contracts;
using System.Xml;
using Microsoft.MSE;
using System.Globalization;
using System.Xml.Schema;



namespace Microsoft.MSE.Catalog.Module.Data
{
    class Endpoint
    {

        #region Endpoint functions

        public static Guid CreateEndpoint(DTO.Endpoint endpoint, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateEndpoint]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter PortParam = new SqlParameter("@Port", SqlDbType.Int);
                    SqlParameter BindingIDParam = new SqlParameter("@BindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter NamespaceURIParam = new SqlParameter("@NamespaceURI", SqlDbType.NText);
                    SqlParameter ServiceNameParam = new SqlParameter("@ServiceName", SqlDbType.NText);
                    SqlParameter ConfigurationParam = new SqlParameter("@Configuration", SqlDbType.Xml);
                    SqlParameter TestCallsAcceptedParam = new SqlParameter("@TestCallsAccepted", SqlDbType.Bit);
                    SqlParameter AutoOperationPubUpdateParam = new SqlParameter("@AutoOperationPubUpdate", SqlDbType.Bit);
                    SqlParameter AutoOperationActivationUpdateParam = new SqlParameter("@AutoOperationActivationUpdate", SqlDbType.Bit);
                    SqlParameter OptimizeWsdlParam = new SqlParameter("@OptimizeWsdl", SqlDbType.Bit);
                    SqlParameter WrapMessageParam = new SqlParameter("@WrapMessage", SqlDbType.Bit);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    // Remove Uri Scheme
                    NameParam.Value = CatalogSvcHelper.RemoveUriScheme(endpoint.Path);
                    if (endpoint.Policy != null)
                    {
                        PolicyIDParam.Value = endpoint.Policy.ID;
                    }
                    else
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }
                    if (endpoint.Binding != null)
                    {
                        if (endpoint.Binding.ID.Equals(Guid.Empty))
                        {
                            BindingIDParam.Value = Binding.CreateMSEBinding(endpoint.Binding, txn, conn);
                        }
                        else
                        {
                            BindingIDParam.Value = endpoint.Binding.ID;
                        }
                    }
                    else
                    {
                        throw new ApplicationException("CreateEndpoint requires Binding, but no Binding was supplied");
                    }
                    DescriptionParam.Value = endpoint.Description;
                    PortParam.Value = endpoint.Port;
                    NamespaceURIParam.Value = endpoint.NamespaceURI;
                    ServiceNameParam.Value = endpoint.ServiceName;
                    TestCallsAcceptedParam.Value = endpoint.TestCallsAccepted;
                    AutoOperationPubUpdateParam.Value = endpoint.AutoOperationPubUpdate;
                    AutoOperationActivationUpdateParam.Value = endpoint.AutoOperationActivationUpdate;
                    OptimizeWsdlParam.Value = endpoint.OptimizeWsdl;
                    WrapMessageParam.Value = endpoint.WrapMessage;
                    if ((endpoint.Configuration == null) || (string.IsNullOrEmpty(endpoint.Configuration.OuterXml)))
                    {
                        ConfigurationParam.Value = DBNull.Value;
                    }
                    else
                    {
                        ConfigurationParam.Value = endpoint.Configuration.OuterXml;
                    }
                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(PortParam);
                    cmd.Parameters.Add(BindingIDParam);
                    cmd.Parameters.Add(PolicyIDParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(NamespaceURIParam);
                    cmd.Parameters.Add(ConfigurationParam);
                    cmd.Parameters.Add(ServiceNameParam);
                    cmd.Parameters.Add(TestCallsAcceptedParam);
                    cmd.Parameters.Add(AutoOperationPubUpdateParam);
                    cmd.Parameters.Add(AutoOperationActivationUpdateParam);
                    cmd.Parameters.Add(OptimizeWsdlParam);
                    cmd.Parameters.Add(WrapMessageParam);

                    cmd.ExecuteNonQuery();

                    ID = (Guid)IDParam.Value;
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return ID;
        }

        public static DTO.Endpoint UpdateEndpoint(DTO.Endpoint endpoint, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateEndpoint]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter PortParam = new SqlParameter("@Port", SqlDbType.Int);
                    SqlParameter BindingIDParam = new SqlParameter("@BindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);
                    SqlParameter NamespaceURIParam = new SqlParameter("@NamespaceURI", SqlDbType.NText);
                    SqlParameter ServiceNameParam = new SqlParameter("@ServiceName", SqlDbType.NText);
                    SqlParameter ConfigurationParam = new SqlParameter("@Configuration", SqlDbType.Xml);
                    SqlParameter TestCallsAcceptedParam = new SqlParameter("@TestCallsAccepted", SqlDbType.Bit);
                    SqlParameter AutoOperationPubUpdateParam = new SqlParameter("@AutoOperationPubUpdate", SqlDbType.Bit);
                    SqlParameter AutoOperationActivationUpdateParam = new SqlParameter("@AutoOperationActivationUpdate", SqlDbType.Bit);
                    SqlParameter OptimizeWsdlParam = new SqlParameter("@OptimizeWsdl", SqlDbType.Bit);
                    SqlParameter WrapMessageParam = new SqlParameter("@WrapMessage", SqlDbType.Bit);


                    if (endpoint.Policy != null)
                    {
                        PolicyIDParam.Value = endpoint.Policy.ID;
                    }
                    else
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = endpoint.ID;
                    NameParam.Value = endpoint.Path;
                    if (endpoint.Binding != null)
                    {
                        if (endpoint.Binding.ID.Equals(Guid.Empty))
                        {
                            BindingIDParam.Value = Binding.CreateMSEBinding(endpoint.Binding, txn, conn);
                        }
                        else
                        {
                            BindingIDParam.Value = endpoint.Binding.ID;
                        }
                    }
                    else
                    {
                        throw new ApplicationException("UpdateEndpoint requires Binding, but no Binding was supplied");
                    }
                    PortParam.Value = endpoint.Port;
                    DescriptionParam.Value = endpoint.Description;
                    NamespaceURIParam.Value = endpoint.NamespaceURI;
                    ServiceNameParam.Value = endpoint.ServiceName;
                    TestCallsAcceptedParam.Value = endpoint.TestCallsAccepted;
                    AutoOperationPubUpdateParam.Value = endpoint.AutoOperationPubUpdate;
                    AutoOperationActivationUpdateParam.Value = endpoint.AutoOperationActivationUpdate;
                    OptimizeWsdlParam.Value = endpoint.OptimizeWsdl;
                    WrapMessageParam.Value = endpoint.WrapMessage;
                    if ((endpoint.Configuration == null) || (string.IsNullOrEmpty(endpoint.Configuration.OuterXml)))
                    {
                        ConfigurationParam.Value = DBNull.Value;
                    }
                    else
                    {
                        ConfigurationParam.Value = endpoint.Configuration.OuterXml;
                    }
                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(PortParam);
                    cmd.Parameters.Add(BindingIDParam);
                    cmd.Parameters.Add(PolicyIDParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(NamespaceURIParam);
                    cmd.Parameters.Add(ConfigurationParam);
                    cmd.Parameters.Add(ServiceNameParam);
                    cmd.Parameters.Add(TestCallsAcceptedParam);
                    cmd.Parameters.Add(AutoOperationPubUpdateParam);
                    cmd.Parameters.Add(AutoOperationActivationUpdateParam);
                    cmd.Parameters.Add(OptimizeWsdlParam);
                    cmd.Parameters.Add(WrapMessageParam);

                    cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return endpoint;
        }

        public static void DeleteEndpoint(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteEndpoint]";

            //List<Guid> runtimeServerIDs = new List<Guid>();
            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static void AssignEndpointToRuntimeServer(Guid endpointID, Guid runtimeServerID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[AssignEndpointToRuntimeServer]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter RuntimeServerIDParam = new SqlParameter("@RuntimeServerID", SqlDbType.UniqueIdentifier);
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    RuntimeServerIDParam.Value = runtimeServerID;
                    cmd.Parameters.Add(RuntimeServerIDParam);
                    EndpointIDParam.Value = endpointID;
                    cmd.Parameters.Add(EndpointIDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static DTO.EndpointList ListEndpointsForRuntimeServerID(Guid runtimeServerID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.EndpointList list = new DTO.EndpointList();
            const string sprocname = "[ServiceEngine].[ListEndpointsForRuntimeServerID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter runtimeServerIDParam = new SqlParameter("@RuntimeServerID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    runtimeServerIDParam.Value = runtimeServerID;
                    cmd.Parameters.Add(runtimeServerIDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Endpoints = new List<DTO.EndpointListElement>();
                            while (reader.Read())
                            {
                                DTO.EndpointListElement element = new DTO.EndpointListElement();
                                element.ID = reader.GetGuid(0);
                                element.URI = reader.GetString(1);
                                element.Port = reader.GetInt32(2);
                                if (reader.IsDBNull(3))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(3);
                                }
                                list.Endpoints.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.EndpointList ListEndpoints(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.EndpointList list = new DTO.EndpointList();
            const string sprocname = "[ServiceEngine].[ListEndpoints]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Endpoints = new List<DTO.EndpointListElement>();
                            while (reader.Read())
                            {
                                DTO.EndpointListElement element = new DTO.EndpointListElement();
                                element.ID = reader.GetGuid(0);
                                element.URI = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.Endpoints.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.EndpointList ListEndpointsForPolicyID(Guid policyID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.EndpointList list = new DTO.EndpointList();
            const string sprocname = "[ServiceEngine].[ListEndpointsForPolicyID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = policyID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Endpoints = new List<DTO.EndpointListElement>();
                            while (reader.Read())
                            {
                                DTO.EndpointListElement element = new DTO.EndpointListElement();
                                element.ID = reader.GetGuid(0);
                                element.URI = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.Endpoints.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }


            return list;

        }

        public static DTO.EndpointList ListEndpointsForBindingID(Guid bindingID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.EndpointList list = new DTO.EndpointList();
            const string sprocname = "[ServiceEngine].[ListEndpointsForBindingID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@BindingID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = bindingID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Endpoints = new List<DTO.EndpointListElement>();
                            while (reader.Read())
                            {
                                DTO.EndpointListElement element = new DTO.EndpointListElement();
                                element.ID = reader.GetGuid(0);
                                element.URI = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.Endpoints.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.Endpoint GetEndpoint(Guid ID, Guid RuntimeServerID, bool provideWSDL, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.Endpoint endpoint = new DTO.Endpoint();
            const string sprocname = "[ServiceEngine].[GetEndpoint]";

            if (RuntimeServerID == Guid.Empty) provideWSDL = false;

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }


                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            endpoint.ID = reader.GetGuid(0);
                            endpoint.Path = reader.GetString(1);
                            endpoint.Binding = CatalogSvcHelper.AssembleBindingFromBindingElements(Binding.GetMSEBinding(reader.GetGuid(2), txn, conn));
                            if (reader.IsDBNull(3))
                            {
                                endpoint.Policy = null;
                            }
                            else
                            {
                                endpoint.Policy = CatalogSvcHelper.AssemblePolicyFromPolicyAssertion(reader.GetGuid(3), txn, conn);
                                //endpoint.Policy = CatalogSvcHelper.AssemblePolicyFromPolicyAssertion(endpoint.Policy, txn, conn);
                            }
                            if (reader.IsDBNull(4))
                            {
                                endpoint.Description = null;
                            }
                            else
                            {
                                endpoint.Description = reader.GetString(4);
                            }
                            if (reader.IsDBNull(5))
                            {
                                endpoint.NamespaceURI = null;
                            }
                            else
                            {
                                endpoint.NamespaceURI = reader.GetString(5);
                            }
                            endpoint.TestCallsAccepted = reader.GetBoolean(6);
                            endpoint.AutoOperationPubUpdate = reader.GetBoolean(7);
                            endpoint.AutoOperationActivationUpdate = reader.GetBoolean(8);
                            if (reader.IsDBNull(9))
                            {
                                endpoint.Configuration = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(9).CreateReader());
                                endpoint.Configuration = xdoc.DocumentElement;
                            }
                            if (reader.IsDBNull(10))
                            {
                                endpoint.ServiceName = null;
                            }
                            else
                            {
                                endpoint.ServiceName = reader.GetString(10);
                            }
                            if (reader.IsDBNull(11))
                            {
                                endpoint.Port = 0;
                            }
                            else
                            {
                                endpoint.Port = reader.GetInt32(11);
                            }
                            endpoint.OptimizeWsdl = reader.GetBoolean(12);
                            endpoint.WrapMessage = reader.GetBoolean(13);
                        }

                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return endpoint;

        }

        public static DTO.OperationVersionList ListAssociatedOperationsForEndpoint(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.OperationVersionList list = new DTO.OperationVersionList();
            const string sprocname = "[ServiceEngine].[ListAssociatedOperationsForEndpoint]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.OperationVersions = new List<DTO.OperationVersionListElement>();
                            while (reader.Read())
                            {
                                DTO.OperationVersionListElement element = new DTO.OperationVersionListElement();
                                element.ID = reader.GetGuid(0);
                                element.OperationName = reader.GetString(1);
                                DTO.VersionNumber version = new DTO.VersionNumber();
                                version.Major = reader.GetInt32(2).ToString();
                                version.Minor = reader.GetInt32(3).ToString();
                                version.Build = reader.GetInt32(4).ToString();
                                version.Qfe = reader.GetInt32(5).ToString();
                                element.Version = version;
                                element.OperationFQN = reader.GetString(6);
                                list.OperationVersions.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.OperationVersionList ListPublishedOperationsForEndpoint(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.OperationVersionList list = new DTO.OperationVersionList();

            const string sprocname = "[ServiceEngine].[ListPublishedOperationsForEndpoint]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.OperationVersions = new List<DTO.OperationVersionListElement>();
                            while (reader.Read())
                            {
                                DTO.OperationVersionListElement element = new DTO.OperationVersionListElement();
                                element.ID = reader.GetGuid(0);
                                element.OperationName = reader.GetString(1);
                                DTO.VersionNumber version = new DTO.VersionNumber();
                                version.Major = reader.GetInt32(2).ToString();
                                version.Minor = reader.GetInt32(3).ToString();
                                version.Build = reader.GetInt32(4).ToString();
                                version.Qfe = reader.GetInt32(5).ToString();
                                element.Version = version;
                                element.OperationFQN = reader.GetString(6);
                                list.OperationVersions.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static void RemoveEndpointFromRuntimeServer(Guid endpointID, Guid runtimeServerID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveEndpointFromRuntimeServer]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter RuntimeServerIDParam = new SqlParameter("@RuntimeServerID", SqlDbType.UniqueIdentifier);
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    RuntimeServerIDParam.Value = runtimeServerID;
                    cmd.Parameters.Add(RuntimeServerIDParam);
                    EndpointIDParam.Value = endpointID;
                    cmd.Parameters.Add(EndpointIDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

        }

        public static void RemoveEndpointsFromRuntimeServer(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveEndpointsFromRuntimeServer]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                ////get list of endpoints that will be removed
                //DTO.EndpointList epList = DAL.ListEndpointsForRuntimeServerID(ID, txn, conn);

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

                ////send notification to affected runtimes
                //List<DTO.DBEventDataItem> eventData = new List<DTO.DBEventDataItem>();
                //epList.Endpoints.ForEach(item => { eventData.Add(new DTO.DBEventDataItem(ID, item.ID)); });
                //DataAccess.RepositoryChange.RecordChange(DTO.DBEvents.EndpointsUpdated, DTO.DBChangeType.Delete, eventData, txn, conn);

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

        }

        public static Guid GetIDForEndpoint(string name, int port, string scheme, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[GetIDForEndpoint]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter PortParam = new SqlParameter("@Port", SqlDbType.Int);
                    SqlParameter SchemeParam = new SqlParameter("@Scheme", SqlDbType.NVarChar, 64);
                    SqlParameter IDParam = new SqlParameter("@EndPointID", SqlDbType.UniqueIdentifier);
                    IDParam.Direction = ParameterDirection.Output;


                    cmd.CommandType = CommandType.StoredProcedure;

                    PortParam.Value = port;
                    NameParam.Value = name;
                    SchemeParam.Value = scheme;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(PortParam);
                    cmd.Parameters.Add(SchemeParam);

                    cmd.ExecuteNonQuery();
                    if (IDParam.Value != DBNull.Value)
                        ID = (Guid)IDParam.Value;
                    else
                        ID = Guid.Empty;
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return ID;
        }

        #endregion

        #region Endpoint Group functions

        public static List<DTO.EndpointListElement> ListEndpointsByGroup(Guid entityGroupID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.EndpointListElement> list = new List<DTO.EndpointListElement>();
            const string sprocname = "[ServiceEngine].[ListEntitiesByGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@entityGroupID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = entityGroupID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list = new List<DTO.EndpointListElement>();
                            while (reader.Read())
                            {
                                DTO.EndpointListElement element = new DTO.EndpointListElement();
                                element.ID = reader.GetGuid(0);
                                element.URI = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        #endregion

        #region UDDI related functions

        public static List<DTO.EndpointAssociatedUddi> ListEndpointUddiAssociation(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[ListEndpointUddiAssociation]";
            List<DTO.EndpointAssociatedUddi> uddiAssociations = new List<DTO.EndpointAssociatedUddi>();

            try
            {

                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            DTO.EndpointAssociatedUddi endpointAssociatedUddi = new DTO.EndpointAssociatedUddi();
                            endpointAssociatedUddi.UddiAssociationID = reader.GetGuid(0);
                            endpointAssociatedUddi.EndpointID = reader.GetGuid(1);
                            endpointAssociatedUddi.TModelKey = reader.GetString(2);
                            endpointAssociatedUddi.ServiceKey = reader.GetString(3);
                            endpointAssociatedUddi.BindingKey = reader.GetString(4);
                            endpointAssociatedUddi.WsdlUrl = reader.GetString(5);
                            endpointAssociatedUddi.BindingUrl = reader.GetString(6);
                            endpointAssociatedUddi.RuntimeServerID = reader.GetGuid(7);
                            uddiAssociations.Add(endpointAssociatedUddi);
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return uddiAssociations;

        }

        public static void PublishEndPointToUddi(DTO.Endpoint endpoint, DTO.RuntimeServer runtimeServer, bool refresh, SqlTransaction txn, SqlConnection conn)
        {
            if (Configurator.svcConfig.serviceCatalogUddi == null ||
                Configurator.svcConfig.serviceCatalogUddi.UddiIntegrationMode == false)
                return;

            bool autoclose = false;

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                if (refresh == true)
                {
                    RemoveEndPointFromUddi(endpoint, runtimeServer, txn, conn);
                }

                // Check if the Endpoint is valid for publishing
                if (endpoint.PubOpVersionsList != null &&
                    endpoint.PubOpVersionsList.OperationVersions != null &&
                    endpoint.PubOpVersionsList.OperationVersions.Count > 0)
                {
                    bool isUpdate = false;
                    bool isNew = false;

                    DTO.EndpointAssociatedUddi endpointAssociatedUddi = GetUddiAssociation(endpoint, runtimeServer);
                    if (endpointAssociatedUddi != null
                            && String.IsNullOrEmpty(endpointAssociatedUddi.ServiceKey) == false
                            && String.IsNullOrEmpty(endpointAssociatedUddi.BindingKey) == false
                            && String.IsNullOrEmpty(endpointAssociatedUddi.TModelKey) == false)
                    {
                        // Update only if anything has changed
                        Uri serviceUri = GetEndpointServiceUri(endpoint, runtimeServer);
                        if (string.Compare(serviceUri.ToString(), endpointAssociatedUddi.BindingUrl, true) != 0)
                        {
                            isUpdate = true;
                        }
                    }
                    else
                    {
                        isNew = true;
                    }
                    if (isUpdate || isNew)
                    {
                        // Publish the Endpoint to UDDI
                        using (IUddiPublisher uddiPublisher = UddiPublisherFactory.GetUddiPublisher(Configurator.svcConfig.serviceCatalogUddi))
                        {
                            if (isUpdate)
                            {
                                endpoint = uddiPublisher.UpdateEndpointToUddi(endpoint, runtimeServer);
                            }
                            if (isNew)
                            {
                                endpoint = uddiPublisher.PublishEndpointToUddi(endpoint, runtimeServer);
                            }
                        }
                        // Get the newly created or updates association
                        endpointAssociatedUddi = GetUddiAssociation(endpoint, runtimeServer);
                        string procName = "";
                        if (isUpdate)
                            procName = "[ServiceEngine].[UpdateEndpointUddiAssociation]";
                        else
                            procName = "[ServiceEngine].[CreateEndpointUddiAssociation]";
                        // Add the association to database
                        using (SqlCommand cmd = new SqlCommand(procName, conn, txn))
                        {
                            SqlParameter ServiceKeyParam = null;
                            SqlParameter TModelKeyParam = null;
                            SqlParameter BindingKeyParam = null;
                            if (isUpdate == false)
                            {
                                ServiceKeyParam = new SqlParameter("@ServiceKey", SqlDbType.NVarChar, 64);
                                TModelKeyParam = new SqlParameter("@TModelKey", SqlDbType.NVarChar, 64);
                                BindingKeyParam = new SqlParameter("@BindingKey", SqlDbType.NVarChar, 64);
                            }
                            SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);
                            SqlParameter RuntimeServerIDParam = new SqlParameter("@RuntimeServerID", SqlDbType.UniqueIdentifier);
                            SqlParameter WsdlUrlParam = new SqlParameter("@WsdlUrl", SqlDbType.NVarChar, 400);
                            SqlParameter BindingUrlParam = new SqlParameter("@BindingUrl", SqlDbType.NVarChar, 400);

                            cmd.CommandType = CommandType.StoredProcedure;

                            if (isUpdate == false)
                            {
                                ServiceKeyParam.Value = endpointAssociatedUddi.ServiceKey;
                                TModelKeyParam.Value = endpointAssociatedUddi.TModelKey;
                                BindingKeyParam.Value = endpointAssociatedUddi.BindingKey;
                            }
                            EndpointIDParam.Value = endpoint.ID;
                            RuntimeServerIDParam.Value = runtimeServer.ID;
                            WsdlUrlParam.Value = endpointAssociatedUddi.WsdlUrl;
                            BindingUrlParam.Value = endpointAssociatedUddi.BindingUrl;

                            if (isUpdate == false)
                            {
                                cmd.Parameters.Add(ServiceKeyParam);
                                cmd.Parameters.Add(TModelKeyParam);
                                cmd.Parameters.Add(BindingKeyParam);
                            }
                            cmd.Parameters.Add(EndpointIDParam);
                            cmd.Parameters.Add(RuntimeServerIDParam);
                            cmd.Parameters.Add(WsdlUrlParam);
                            cmd.Parameters.Add(BindingUrlParam);

                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static Uri GetEndpointServiceUri(DTO.Endpoint endpoint, DTO.RuntimeServer runtimeServer)
        {
            Uri serviceUri;

            System.ServiceModel.Channels.Binding myBinding = BindingHelper.DeserializeBinding(endpoint.Binding.BindingConfig);
            try
            {
                string uri = "";
                if (endpoint.Port > 0)
                    uri = string.Format("{0}://{1}:{2}/{3}", myBinding.Scheme, runtimeServer.HostName, endpoint.Port, endpoint.Path);
                else
                    uri = string.Format("{0}://{1}/{2}", myBinding.Scheme, runtimeServer.HostName, endpoint.Path);
                serviceUri = new Uri(uri);
            }
            catch { serviceUri = null; }

            return serviceUri;
        }

        public static void RemoveEndPointFromUddi(DTO.Endpoint endpoint, DTO.RuntimeServer runtimeServer, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveEndpointUddiAssociation]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                // Remove the Endpoint from UDDI
                if (Configurator.svcConfig.serviceCatalogUddi != null &&
                    Configurator.svcConfig.serviceCatalogUddi.UddiIntegrationMode == true)
                {
                    using (IUddiPublisher uddiPublisher = UddiPublisherFactory.GetUddiPublisher(Configurator.svcConfig.serviceCatalogUddi))
                    {
                        uddiPublisher.RemoveEndpointFromUddi(endpoint, runtimeServer);
                    }
                }
                // Remove the Endpoint association from Database
                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);
                    SqlParameter RuntimeServerIDParam = new SqlParameter("@RuntimeServerID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    EndpointIDParam.Value = endpoint.ID;
                    RuntimeServerIDParam.Value = runtimeServer.ID;

                    cmd.Parameters.Add(EndpointIDParam);
                    cmd.Parameters.Add(RuntimeServerIDParam);
                    cmd.ExecuteNonQuery();

                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

        }

        internal static DTO.EndpointAssociatedUddi GetUddiAssociation(DTO.Endpoint endPoint, DTO.RuntimeServer runtimeServer)
        {
            DTO.EndpointAssociatedUddi endpointAssociatedUddi = null;

            if (endPoint.UddiAssociations != null && endPoint.UddiAssociations.Count > 0)
            {
                foreach (DTO.EndpointAssociatedUddi eua in endPoint.UddiAssociations)
                {
                    if (eua.EndpointID.CompareTo(endPoint.ID) == 0 &&
                        eua.RuntimeServerID.CompareTo(runtimeServer.ID) == 0)
                    {
                        endpointAssociatedUddi = eua;
                        break;
                    }
                }
            }
            return endpointAssociatedUddi;
        }

        public static DTO.EndpointList ListAssociatedEndpointsForOperation(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.EndpointList list = new DTO.EndpointList();
            const string sprocname = "[ServiceEngine].[ListAssociatedEndpointsForOperationID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@OperationID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Endpoints = new List<DTO.EndpointListElement>();
                            while (reader.Read())
                            {
                                DTO.EndpointListElement element = new DTO.EndpointListElement();
                                element.ID = reader.GetGuid(0);
                                element.URI = reader.GetString(1);
                                list.Endpoints.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;
        }

        #endregion

    }
}
